﻿/****************************** Module Header ******************************\
 * Module Name:  SQLiteHelper.cs
 * Module Version:  1.0.0 (2013-6-5)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database. It supplies public method to execute 
 * a sql command or get a common connection or get the database schema infomation.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Xml.Linq;
using System.Data.Common;
using System.Text;
using System.Data.SQLite;

namespace UCL.Data
{
    /// <summary>
    /// SQLite助理类
    /// </summary>
    public class SQLiteHelper : IDatabaseHelper
    {
        /// <summary>
        /// 获取或设置数据库连接信息
        /// </summary>
        public ConnectionInfo ConnInfo { get; private set; }

        /// <summary>
        /// 获取数据库SQL语句模板
        /// </summary>
        private SqlTemplate sqlTemplate;

        /// <summary>
        /// 获取数据库SQL语句模板
        /// </summary>
        public SqlTemplate SqlTemplate { get { return sqlTemplate; } }

        /// <summary>
        /// 获取或设置当前连接的数据库名称
        /// </summary>
        public string DatabaseName { get; set; }

        /// <summary>
        /// 获取或设置连接超时时间
        /// </summary>
        public int Timeout { get; set; }

        /// <summary>
        /// 获取或设置当前连接的数据库服务器名称
        /// </summary>
        public string ServerName { get; set; }

        /// <summary>
        /// 获取或设置当前处理的数据表名称
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 获取或设置数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 获取数据源提供程序名称
        /// </summary>
        public string ProviderName { get { return "System.Data.SQLite"; } }

        /// <summary>
        /// 获取或设置消息收集器
        /// </summary>
        public IList<string> MessageSet { get; set; }

        /// <summary>
        /// 获取或设置是否启用调试模式,如果启用，则错误消息将包含敏感信息
        /// </summary>
        public bool IsDebug { get; set; }
                
        /// <summary>
        /// 构造方法
        /// </summary>
        public SQLiteHelper()
        {
            this.ConnInfo.Provider = "System.Data.SqlClient";
            this.sqlTemplate = new SqlTemplate()
            {
                ShowTableCommonSql = "select f.value as Common from sysobjects d left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where d.name='{0}'",
                DescribeTableSql = "SELECT ColOrder = a.colorder, Name = a.name, IsIdentity = COLUMNPROPERTY( a.id,a.name,'IsIdentity'), KeyName = (SELECT name FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))), DbType  = b.name, Length = a.length, Precision = COLUMNPROPERTY(a.id,a.name,'PRECISION'), XScale   = COLUMNPROPERTY(a.id,a.name,'Scale'), AllowDBNull= a.isnullable, DefaultValue = e.text, Common = g.[value] FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id where d.name='{0}'",
                ShowTablesSql = "select [name] from [sysobjects] where [xtype]='u'",
                ShowProceduresSql = "select [name] from [sysobjects] where [xtype]='p'",
                ShowViewsSql = "select [name] from [sysobjects] where [xtype]='v'",
                ShowDatabasesSql = "sp_databases",
                ShowFieldsSql = "select name from syscolumns where id=object_id('{0}') order by colid",
                AutoIncrementSql = "identity(1,1)",
                DropTableSql = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) " + Environment.NewLine + "DROP TABLE [dbo].[{0}];" + Environment.NewLine,
                RenameTableSql = "EXEC sp_rename '{0}','{1}';",
                TruncateTableSql = "Truncate Table [{0}];",
                RenameProcedureSql = "exec sp_rename '{0}','{1}','OBJECT'",
                RenameViewSql = "exec sp_rename '{0}','{1}'",
                DropViewSql = "drop view {0}",
            };
            this.MessageSet = new List<string>();
        }

        /// <summary>
        /// 构造方法
        /// </summary>
        public SQLiteHelper(string connectionString)
            : this()
        {
            this.ConnInfo.Url = connectionString;
        }

        #region 数据库普通操纵方法

        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(commandText)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    conn.Open();
                    try
                    {
                        intOut = cmd.ExecuteNonQuery();
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return intOut;
        }

        /// <summary>
        /// 执行命令并返回一个数据表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, DbParameter[] paras = null)
        {
            DataTable dt = new DataTable();
            if (string.IsNullOrEmpty(commandText)) return dt;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    DbDataAdapter dbda = new SQLiteDataAdapter();
                    dbda.SelectCommand = cmd;
                    conn.Open();
                    try
                    {
                        dbda.Fill(dt);
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取分页数据表 2013-6-10 by sutroon
        /// </summary>
        /// <param name="fromsql">如：from tmytest where State=1</param>
        /// <param name="orderby">如：CreatedTime desc</param>
        /// <param name="fields">如：*</param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable ExecutePagingDataTable(string fromsql, string orderby, string fields, out int recordcount, out int pagecount, int pageid = 1, int pagesize = 20)
        {
            recordcount = Convert.ToInt32(this.GetValue(string.Format("select count(0) {0};", fromsql)));
            pagecount = recordcount.Ceil(pagesize);
            if (!orderby.Trim().ToLower().StartsWith("order by ")) orderby = string.Format(" order by {0} ", orderby);
            return this.ExecuteDataTable(string.Format("select {0} {1} {2} limit {3},{4};", fields, fromsql, orderby, ((pageid - 1) * pagesize), pagesize));
        }

        /// <summary>
        /// 执行命令并返回一个数据集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, DbParameter[] paras = null)
        {
            DataSet ds = new DataSet();
            if (string.IsNullOrEmpty(commandText)) return ds;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    DbDataAdapter dbda = new SQLiteDataAdapter();
                    dbda.SelectCommand = cmd;
                    conn.Open(); try
                    {
                        dbda.Fill(ds);
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 递归获取分类数据表,数据主要用于DropDownList控件
        /// </summary>
        /// <param name="sqlQuery">查询语句,如：select id,name from catalog where parentid={0} and extags='album' order by Ordinal</param>
        /// <param name="parentID">父编号,一般是0</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '_'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        /// <param name="topID"></param>
        /// <param name="topName"></param>
        /// <returns></returns>
        public DataTable ExecuteTreeTable(string sqlQuery, string parentID, char paddingChar = '_', int loopCount = 0, string topID = "0", string topName = "请选择")
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("Level");
            if (!string.IsNullOrEmpty(topName))
            {
                dt.Rows.Add(topID, topName, 0);
            }
            ExecuteTreeTable_Func(sqlQuery, parentID, ref dt, paddingChar, loopCount);
            return dt;
        }
        // ExecuteTreeTable递归方法
        private void ExecuteTreeTable_Func(string sqlQuery, string parentID, ref DataTable dt, char paddingChar, int loopCount)
        {
            bool isTree = false;
            string sqlSearch = sqlQuery;
            if (sqlQuery.Contains('{'))
            {
                isTree = true;
                sqlSearch = sqlQuery.Replace("{0}", parentID);
            }

            using (DbConnection conn = GetDbConnection())
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlSearch;
                conn.Open();
                int i = loopCount;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        dt.Rows.Add(dr[0].ToString(), "".PadRight(i, paddingChar) + dr[1].ToString(), i.ToString());
                        if (isTree) ExecuteTreeTable_Func(sqlQuery, dr[0].ToString(), ref dt, paddingChar, ++loopCount);
                    }
                }
            }
        }

        /// <summary>
        /// 获取当前节点的所有子节点(包含节点关联的元素)和递归父节点树形列表
        /// <para>例如：ExecuteTreeXml("Select ID, Name from tUSerDepartment where ParentID='{0}'", "093F207D-634B-4717-AB50-91A21F035083", "Select AgentID, Name from tUserMember where DepartmentID='{0}'", "select ID, Name, ParentID from tUserDepartment where ID='{0}'"));</para>
        /// </summary>
        /// <param name="sqlChildFmt">如：Select ID, Name from tUSerDepartment where ParentID='{0}'</param>
        /// <param name="nodeID">当前节点的值</param>
        /// <param name="sqlfmtItems">如：Select AgentID, Name, Face, State from tUserMember where DepartmentID='{0}' (DepartmentID是外键，对应部门表的主键)</param>
        /// <param name="sqlfmtParent">如：select ID, Name, ParentID from tUserDepartment where ID='{0}' (需要通过Parent来追溯父级链，并且ParentID必需是第三个查询的字段！)</param>
        /// <returns>树形XML字符串</returns>
        /// <remarks>
        /// 2013-9-5 1.0.0 by sutroon 创建
        /// 2013-9-9 2.0.0 by sutroon 重构,新增父级链表重塑和子集项目关联
        /// 2013-10-29 2.0.1 by sutroon 支持字段映射到属性功能
        /// </remarks>
        public string ExecuteTreeXml(string sqlChildFmt, string nodeID, string sqlfmtItems = "", string sqlfmtParent = "")
        {
            StringBuilder sb = new StringBuilder(), sbChild = new StringBuilder(), sbParent = new StringBuilder();
            sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root>");
            if (!string.IsNullOrEmpty(sqlChildFmt)) ExecuteTreeXmlForChild_Func(sqlChildFmt, nodeID, ref sbChild, sqlfmtItems);
            if (string.IsNullOrEmpty(sqlfmtParent))
            {
                sb.Append(sbChild.ToString());
            }
            else
            {
                sbParent.Append(sbChild.ToString());
                ExecuteTreeXmlForParent_Func(sqlfmtParent, nodeID, ref sbParent);
                sb.Append(sbParent.ToString());
            }
            sb.Append("</root>");
            return sb.ToString();
        }
        // 获取所有子集以及子集中的所有项目的方法
        private void ExecuteTreeXmlForChild_Func(string sqlfmt, string pid, ref StringBuilder sb, string sqlfmtItems = "")
        {
            if (string.IsNullOrEmpty(sqlfmt)) return;
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            DataTable dt2;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.AppendFormat("<node {0}>", r.ToAttributeString());
                    if (!string.IsNullOrEmpty(sqlfmtItems))
                    {
                        dt2 = ExecuteDataTable(string.Format(sqlfmtItems, r[0]));
                        if (dt2.Rows.Count > 0)
                        {
                            foreach (DataRow r2 in dt2.Rows)
                            {
                                sb.AppendFormat("<node {0} />", r2.ToAttributeString());
                            }
                        }
                    }
                    ExecuteTreeXmlForChild_Func(sqlfmt, r[0].ToString(), ref sb);
                    sb.Append("</node>");
                }
            }
        }
        // 获取当前节点所有父节点链表的方法
        private void ExecuteTreeXmlForParent_Func(string sqlfmt, string pid, ref StringBuilder sb)
        {
            if (string.IsNullOrEmpty(sqlfmt)) return;
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.Insert(0, string.Format("<node {0}>", r.ToAttributeString()));
                    ExecuteTreeXmlForParent_Func(sqlfmt, r[2].ToString(), ref sb);
                    sb.Append("</node>");
                }
            }
        }

        /// <summary>
        /// 获取所有父级编号列表(包括自己)
        /// </summary>
        /// <param name="sqltpl">如: select ParentID from tTable where ID='{0}' order by Ordinal</param>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<string> GetParentIds(string sqltpl, string id = "")
        {
            IList<string> list = new List<string>();
            if (string.IsNullOrEmpty(id))
            {
                return list;
            }
            this.GetParentIdsFnc(ref list, sqltpl, id);
            return list;
        }
        // GetParentIds子方法
        private void GetParentIdsFnc(ref IList<string> list, string tplSql, string id)
        {
            foreach (DataRow r in ExecuteDataTable(tplSql.Fmt(id)).Rows)
            {
                list.Add(r[0].ToString());
                this.GetParentIdsFnc(ref list, tplSql, r[0].ToString());
            }
        }

        /// <summary>
        /// 获取所有子级编号列表(不包括自己)
        /// </summary>
        /// <param name="sqltpl">如：select ID from tTable where ParentID='{0}'</param>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <remarks>2015-1-29 by sutroon</remarks>
        public IList<string> GetChildIds(string sqltpl, string id = "")
        {
            IList<string> list = new List<string>();
            this.GetChildIDsFnc(ref list, sqltpl, id);
            return list;
        }
        private void GetChildIDsFnc(ref IList<string> list, string sqlTemplate, string parentID)
        {
            foreach (DataRow dr in ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                list.Add(dr[0].ToString());
                GetChildIDsFnc(ref list, sqlTemplate, dr[0].ToString());
            }
        }

        /// <summary>
        /// 获取指定行的所有值的列表
        /// </summary>
        /// <typeparam name="V">指定行的数据类型</typeparam>
        /// <param name="sqlQuery">查询语句</param>
        /// <returns></returns>
        public IList<V> GetColumnValues<V>(string sqlQuery)
        {
            IDataReader idr = this.ExecuteDataTable(sqlQuery).CreateDataReader();
            IList<V> list = new List<V>();
            while (idr.Read())
            {
                list.Add((V)Convert.ChangeType(idr[0], typeof(V)));
            }
            return list;
        }

        /// <summary>
        /// 获取值 2013-6-9 by sutroon
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetValue(string sql, object defv = null)
        {
            using (DbConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    object v = cmd.ExecuteScalar();
                    return v == DBNull.Value ? defv : v;
                }
            }
        }

        public string SelectString(string tableName, string search, string fields = "*", string defv = "")
        {
            if (!string.IsNullOrEmpty(search)) search = " where " + search;
            object v = this.GetValue(string.Format("select {0} from {1}{2}", fields, tableName, search));
            return v == null ? defv : v.ToString();
        }

        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public int Update(string tableName, string fields, string search)
        {
            return this.ExecuteNonQuery(string.Format("update {0} set {1} where {2}", tableName, fields, search));
        }

        /// <summary>
        /// 插入数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int Insert(string tableName, string fields, string values)
        {
            return this.ExecuteNonQuery(string.Format("insert into {0} ({1}) values ({2})", tableName, fields, values));
        }

        /// <summary>
        /// 删除数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public int Delete(string tableName, string search)
        {
            if (!string.IsNullOrEmpty(search)) search = " where " + search;
            return this.ExecuteNonQuery(string.Format("delete from {0}{1}", tableName, search));
        }

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks>2013-11-3 by sutroon</remarks>
        public bool Exists(string tableName, string search, string field = "id")
        {
            return Count(tableName, search, field) > 0;
        }

        /// <summary>
        /// 查询记录数量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks>2013-11-3 by sutroon</remarks>
        public int Count(string tableName, string search, string field = "id")
        {
            return (int)this.GetValue(string.Format("select count({0}) from {1}{2}", field, tableName, search));
        }

        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="fields"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable SelectDataTable(string tableName, string search, string fields = "*",int count=0)
        {
            return ExecuteDataTable(string.Format("select {0} from {1}{2}{3}", fields, tableName, string.IsNullOrEmpty(search) ? "" : " where ", search));
        }

        /// <summary>
        /// 上移 2013-6-10 by sutroon
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="id"></param>
        /// <param name="idPrev"></param>
        public void MoveUp(string tablename, int id, int idPrev)
        {
            int ordinal = this.ExecuteDataTable(string.Format("select Ordinal from {0} where ID={1};", tablename, idPrev)).Rows[0][0].ToString().ToInt();

            this.Update(tablename, "Ordinal=" + (ordinal + 1), "ID=" + idPrev);
            this.Update(tablename, "Ordinal=" + ordinal, "ID=" + id);
        }
        public void MoveDown(string tablename, int id, int idNext)
        {
            int ordinal = this.ExecuteDataTable(string.Format("select Ordinal from {0} where ID={1};", tablename, idNext)).Rows[0][0].ToString().ToInt();

            this.Update(tablename, "Ordinal=" + (ordinal - 1), "ID=" + idNext);
            this.Update(tablename, "Ordinal=" + ordinal, "ID=" + id);
        }
        #endregion

        #region 获取基础对象方法

        /// <summary>
        /// 获取通用数据库连接对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbConnection GetDbConnection()
        {
            DbConnection conn = new SQLiteConnection();
            conn.ConnectionString = ConnectionString;
            return conn;
        }

        /// <summary>
        /// 获取通用数据库命令对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbCommand GetDbCommand()
        {
            return GetDbConnection().CreateCommand();
        }
        #endregion
        
        #region 存储过程操纵方法

        /// <summary>
        /// 执行存储过程,返回新的参数值集合
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public Dictionary<string, object> ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters)
        {
            int iOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.Parameters.AddRange(dbParameters);
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.CommandText = procedureName;
                conn.Open();
                iOut = oCmd.ExecuteNonQuery();
            }
            return dbParameters.ToDictionary();
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters)
        {
            DataTable oDt = new DataTable();
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = procedureName;
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.Parameters.AddRange(dbParameters);
                DbDataAdapter oDa = new SQLiteDataAdapter();
                oDa.SelectCommand = oCmd;
                conn.Open();
                oDa.Fill(oDt);
            }
            if (outParameters == null) return oDt;
            foreach (DbParameter para in dbParameters)
            {
                outParameters[para.ParameterName] = para.Value;
            }
            return oDt;
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters)
        {
            Dictionary<string, object> dict = null;
            return this.ExecuteProcedureDataTable(procedureName, ref dict, dbParameters);
        }

        #endregion
        
        /// <summary>
        /// 设置数量限制
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public string SetLimitSql(string sql, string limit)
        {
            sql = sql.TrimStart();
            if (sql.Substring(0, 7).ToLower() != "select ") return sql;
            return sql.Insert(6, " top {0}".Fmt(limit));
        }

        /// <summary>
        /// 格式化为指定数据库产品的sql命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string FormatSql(string sql)
        {
            return sql;
        }


        public string GetConnectionString(string serverName, string loginID, string password, string database = "")
        {
            throw new NotImplementedException();
        }

        public bool TestConnection(out string message)
        {
            throw new NotImplementedException();
        }
    }
}
